{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Python通过折线图发现产品流量问题"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "折线图：显示随时间而变化的连续数据，展示在相等时间间隔下数据的趋势。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "实例：数据来自kaggle网站的\"E-commerce website Funnel analysis\"  \n",
    "地址为：https://www.kaggle.com/aerodinamicc/ecommerce-website-funnel-analysis\n",
    "\n",
    "网站很简单，有四个页面数据：\n",
    "1. home_page_table.csv，首页用户访问数据\n",
    "2. search_page_table.csv，搜索页用户访问数据\n",
    "3. payment_page_table.csv，支付信息页用户访问数据\n",
    "4. payment_confirmation_table.csv，支付成功页用户访问数据\n",
    "5. user_table.csv，用户信息数据\n",
    "\n",
    "目标：绘制转化率的折线图，查看是否有异常情况"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1. 读取数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "import pyecharts.options as opts\n",
    "from pyecharts.charts import Line"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 读取5个数据表到df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_home_page = pd.read_csv(\"./datas/ecommerce-website-funnel-analysis/home_page_table.csv\")\n",
    "df_search_page = pd.read_csv(\"./datas/ecommerce-website-funnel-analysis/search_page_table.csv\")\n",
    "df_payment_page = pd.read_csv(\"./datas/ecommerce-website-funnel-analysis/payment_page_table.csv\")\n",
    "df_payment_confirmation_page = pd.read_csv(\"./datas/ecommerce-website-funnel-analysis/payment_confirmation_table.csv\")\n",
    "df_user_table = pd.read_csv(\"./datas/ecommerce-website-funnel-analysis/user_table.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>page_home</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>313593</td>\n",
       "      <td>home_page</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>468315</td>\n",
       "      <td>home_page</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>264005</td>\n",
       "      <td>home_page</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>290784</td>\n",
       "      <td>home_page</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>639104</td>\n",
       "      <td>home_page</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   user_id  page_home\n",
       "0   313593  home_page\n",
       "1   468315  home_page\n",
       "2   264005  home_page\n",
       "3   290784  home_page\n",
       "4   639104  home_page"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_home_page.rename(columns={\"page\":\"page_home\"}, inplace=True)\n",
    "df_home_page.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>page_search</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>15866</td>\n",
       "      <td>search_page</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>347058</td>\n",
       "      <td>search_page</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>577020</td>\n",
       "      <td>search_page</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>780347</td>\n",
       "      <td>search_page</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>383739</td>\n",
       "      <td>search_page</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   user_id  page_search\n",
       "0    15866  search_page\n",
       "1   347058  search_page\n",
       "2   577020  search_page\n",
       "3   780347  search_page\n",
       "4   383739  search_page"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_search_page.rename(columns={\"page\":\"page_search\"}, inplace=True)\n",
    "df_search_page.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>page_payment</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>253019</td>\n",
       "      <td>payment_page</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>310478</td>\n",
       "      <td>payment_page</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>304081</td>\n",
       "      <td>payment_page</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>901286</td>\n",
       "      <td>payment_page</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>195052</td>\n",
       "      <td>payment_page</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   user_id  page_payment\n",
       "0   253019  payment_page\n",
       "1   310478  payment_page\n",
       "2   304081  payment_page\n",
       "3   901286  payment_page\n",
       "4   195052  payment_page"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_payment_page.rename(columns={\"page\":\"page_payment\"}, inplace=True)\n",
    "df_payment_page.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>page_confirmation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>123100</td>\n",
       "      <td>payment_confirmation_page</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>704999</td>\n",
       "      <td>payment_confirmation_page</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>407188</td>\n",
       "      <td>payment_confirmation_page</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>538348</td>\n",
       "      <td>payment_confirmation_page</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>841681</td>\n",
       "      <td>payment_confirmation_page</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   user_id          page_confirmation\n",
       "0   123100  payment_confirmation_page\n",
       "1   704999  payment_confirmation_page\n",
       "2   407188  payment_confirmation_page\n",
       "3   538348  payment_confirmation_page\n",
       "4   841681  payment_confirmation_page"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_payment_confirmation_page.rename(columns={\"page\":\"page_confirmation\"}, inplace=True)\n",
    "df_payment_confirmation_page.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>date</th>\n",
       "      <th>device</th>\n",
       "      <th>sex</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>450007</td>\n",
       "      <td>2015-02-28</td>\n",
       "      <td>Desktop</td>\n",
       "      <td>Female</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>756838</td>\n",
       "      <td>2015-01-13</td>\n",
       "      <td>Desktop</td>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>568983</td>\n",
       "      <td>2015-04-09</td>\n",
       "      <td>Desktop</td>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>190794</td>\n",
       "      <td>2015-02-18</td>\n",
       "      <td>Desktop</td>\n",
       "      <td>Female</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>537909</td>\n",
       "      <td>2015-01-15</td>\n",
       "      <td>Desktop</td>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   user_id        date   device     sex\n",
       "0   450007  2015-02-28  Desktop  Female\n",
       "1   756838  2015-01-13  Desktop    Male\n",
       "2   568983  2015-04-09  Desktop    Male\n",
       "3   190794  2015-02-18  Desktop  Female\n",
       "4   537909  2015-01-15  Desktop    Male"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_user_table.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 关联5个数据表为一个大表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_merge = df_user_table\n",
    "\n",
    "for df_inter in [df_home_page, df_search_page, df_payment_page, df_payment_confirmation_page]:\n",
    "    # 每次循环都会忘df_merge中添加新列\n",
    "    df_merge = pd.merge(\n",
    "        left=df_merge, \n",
    "        right=df_inter, \n",
    "        left_on=\"user_id\", \n",
    "        right_on=\"user_id\", \n",
    "        how=\"left\"\n",
    "    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>date</th>\n",
       "      <th>device</th>\n",
       "      <th>sex</th>\n",
       "      <th>page_home</th>\n",
       "      <th>page_search</th>\n",
       "      <th>page_payment</th>\n",
       "      <th>page_confirmation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>450007</td>\n",
       "      <td>2015-02-28</td>\n",
       "      <td>Desktop</td>\n",
       "      <td>Female</td>\n",
       "      <td>home_page</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>756838</td>\n",
       "      <td>2015-01-13</td>\n",
       "      <td>Desktop</td>\n",
       "      <td>Male</td>\n",
       "      <td>home_page</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>568983</td>\n",
       "      <td>2015-04-09</td>\n",
       "      <td>Desktop</td>\n",
       "      <td>Male</td>\n",
       "      <td>home_page</td>\n",
       "      <td>search_page</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   user_id        date   device     sex  page_home  page_search page_payment  \\\n",
       "0   450007  2015-02-28  Desktop  Female  home_page          NaN          NaN   \n",
       "1   756838  2015-01-13  Desktop    Male  home_page          NaN          NaN   \n",
       "2   568983  2015-04-09  Desktop    Male  home_page  search_page          NaN   \n",
       "\n",
       "  page_confirmation  \n",
       "0               NaN  \n",
       "1               NaN  \n",
       "2               NaN  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_merge.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_merge.columns = [\n",
    "    \"user_id\", \"date\", \"device\", \"sex\", \n",
    "    \"home_page\", \"search_page\", \"payment_page\", \"confirmation_page\"]\n",
    "df_merge.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_merge[\"date\"] = pd.to_datetime(df_merge[\"date\"])\n",
    "df_merge.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2. 展现每个页面整体的PV曲线"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_data = (\n",
    "    df_merge\n",
    "        .groupby(\"date\")\n",
    "        .agg(\n",
    "            home_page=(\"home_page\", lambda x : x.dropna().size),\n",
    "            search_page=(\"search_page\", lambda x : x.dropna().size),\n",
    "            payment_page=(\"payment_page\", lambda x : x.dropna().size),\n",
    "            confirmation_page=(\"confirmation_page\", lambda x : x.dropna().size)\n",
    "        )\n",
    ")\n",
    "df_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 绘制折线图\n",
    "c = (\n",
    "    Line()\n",
    "    .add_xaxis(df_data.index.to_list())\n",
    "    .add_yaxis(\"home_page\", df_data[\"home_page\"].to_list())\n",
    "    .add_yaxis(\"search_page\", df_data[\"search_page\"].to_list())\n",
    "    .add_yaxis(\"payment_page\", df_data[\"payment_page\"].to_list())\n",
    "    .add_yaxis(\"confirmation_page\", df_data[\"confirmation_page\"].to_list())\n",
    "    .set_global_opts(title_opts=opts.TitleOpts(title=\"整体PV折线图\"))\n",
    ")\n",
    "c.render_notebook()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3. 查看分设备的PV曲线"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_data = (\n",
    "    df_merge\n",
    "        .groupby([\"date\", \"device\"])[\"search_page\"]\n",
    "        .agg(search_page=lambda x : x.dropna().size)\n",
    "        .unstack()\n",
    ")\n",
    "df_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "c = (\n",
    "    Line()\n",
    "    .add_xaxis(df_data.index.to_list())\n",
    "    .add_yaxis(\"Desktop\", df_data[(\"search_page\", \"Desktop\")].to_list())\n",
    "    .add_yaxis(\"Mobile\", df_data[(\"search_page\", \"Mobile\")].to_list())\n",
    "    .set_global_opts(title_opts=opts.TitleOpts(title=\"分设备PV趋势图\"))\n",
    ")\n",
    "c.render_notebook()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
